﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;

namespace ReviewCurveApp.Classes
{
    public class DBLoader
    {

        ~DBLoader()
        {
            if (_conn != null) _conn.Close();
        }

        private static MySqlConnection _conn;
        private DataTable data;
        private MySqlDataAdapter da;
        //private MySqlCommandBuilder cb;
        private MySqlCommand cmd = new MySqlCommand();
       // private MySqlDataReader dr ;


        /// <summary>
        /// 获得连接字符串
        /// </summary>
        /// <returns></returns>
        private string getConnStr()
        {
            return getConnStr_Local();
            //return getConnStr_Remote();
            
            
        }

        /// <summary>
        /// 获得连接字符串
        /// </summary>
        /// <returns></returns>
        private string getConnStr_Local()
        {
             string connStr = String.Format(
               "server={0};user id={1}; password={2}; database=mysql; pooling=false"
               , "localhost"
               , "root"
               , "root"
               );
             return connStr;
        }

        /// <summary>
        /// 获得连接字符串
        /// </summary>
        /// <returns></returns>
        private string getConnStr_Remote()
        {
            string connStr = String.Format(
               "server={0};user id={1}; password={2}; database=mysql; pooling=false"
               , "192.168.199.175"
               , "sa"
               , "111"
               );
            return connStr;

        }

        private MySqlConnection getConnect()
        {
            if (_conn == null) CreateConn();
            
            if (_conn.State != ConnectionState.Open) _conn.Open();

            return _conn;
        }

        private void CreateConn()
        {
            _conn = new MySqlConnection(getConnStr());
            _conn.Open();
        }


   
        public bool test()
        {
           
            data = new DataTable();
            string sql = string.Format("INSERT INTO test.Units VALUES(NULL, @UnitName, @Description)");

           
            cmd.Connection = getConnect();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@UnitName", "testName");
            cmd.Parameters.AddWithValue("@Description", "testDesc");
            cmd.ExecuteNonQuery();
            return true;
            //da = new MySqlDataAdapter(sql , conn);
            //cb = new MySqlCommandBuilder(da);
            

            

            //da.Fill(data);

            //dataGrid.DataSource = data;
        }


        internal bool ExcuteQuery(MyDBCommand command)
        {
            try
            {
                cmd.Connection = getConnect();
                cmd.CommandText = command.Sql;
                AddParameters(command.Parameters);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch 
            {
                return false;
            }

        }

        internal bool ExcuteTranscation(List<MyDBCommand> commands)
        {
            MySqlConnection myConn = getConnect();
            MySqlTransaction trans = myConn.BeginTransaction();
            cmd.Transaction = trans;
            try
            {

                for (int i = 0; i < commands.Count; i++)
                {
                    cmd.Connection = myConn;

                    cmd.CommandText =commands[i].Sql;

                    AddParameters(commands[i].Parameters);

                    cmd.ExecuteNonQuery();                   
                }

                trans.Commit();

                return true;
            }
            catch 
            {
                trans.Rollback();

                return false;
            }

        }

        internal int InsertAutoCreatedId(MyDBCommand command)
        {
            try
            {
                MySqlConnection con=getConnect();
                cmd.Connection = con;

                cmd.CommandText = command.Sql;

                AddParameters(command.Parameters);              

                int result=cmd.ExecuteNonQuery();
                
                if (result==0)
                {
                    return -1;
                }

                cmd.CommandText = "select LAST_INSERT_ID()";
                object o=cmd.ExecuteScalar();
                return int.Parse(o.ToString());
            }
            catch 
            {
                return -1;
            }

        }

        internal DataTable Query(string sql)
        {
            MyDBCommand command = new MyDBCommand();
            
            command.Sql = sql;
            
            return Query(command);
        }

        internal DataTable Query(MyDBCommand myCommand)
        {
            try
            {

                cmd.Connection = getConnect();
                cmd.CommandText = myCommand.Sql;
                AddParameters(myCommand.Parameters);
                da = new MySqlDataAdapter(cmd);

                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch
            {
                return null;
            }
        }
   
        private void AddParameters(Parameters parameters)
        {
            cmd.Parameters.Clear();

            if (parameters == null) return;

            for (int j = 0; j < parameters.Count; j++)
            {
                cmd.Parameters.AddWithValue(parameters[j].Key, parameters[j].Value);
            }
        }


    }
}
